11th June 2020
Magento 2 Direct SQL Queries in Zend Format
Eecrets Magento
Sometimes due to some project requirements, we need to directly communicate with Magento2 Database and we have to write SQL queries for that. This post will help you to write direct SQL Queries in Zend Format.
public function __construct(
\Magento\Framework\App\ResourceConnection $resourceConnection
) {
$this->resourceConnection = $resourceConnection;
}
$connection = $this->resourceConnection->getConnection();
$tableName = $this->resourceConnection->getTableName('customer_history');
$tableName2 = $this->resourceConnection->getTableName('order_history');
OR
Using Object Manager Directly:
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName =$resource->getTableName('customer_history');
$tableName2 =$resource->getTableName('order_history');
Select Queries:
Syntax:
$select = $connection->select()
->from(
['p' => $tableName])
->where('p.column_name=?', $value)
->where('p.column_name2 >=?', $value)
->order('p.column_name3 DESC')
->limit($pagesize, $offset);
$data = $connection->fetchAll($select);
Example:
$select = $connection->select()
->from(
['p' => $tableName])
->where('p.customer_id=?', 5)
->where('p.status =?', 1)
->order('p.createdat DESC')
->limit(10, 0);
To get only selected columns from Table:
$select = $connection->select()
->from( ['p' => $tableName],['p.order_id', 'p.status'])
->where('p.customer_id=?', 5)
->where('p.status =?', 1)
->order('p.createdat DESC')
->limit(10, 0);
$data = $connection->fetchAll($select);
Perform Select Query with JOIN
$select = $connection->select()
->from(
['p' => $tableName], ['p.customer_id', 'p.status', 'p.order_id','o.ordertotal']
)
->join(
['o' => $tableName2], 'main_table.order_id = order.id', ['']
)
->where('p.order_id = (?)', '455');
$data = $connection->fetchAll($query);
Insert Query:
Syntax:
$data = ["column_name"=>$value,"column_name2"=>$value2,'column_name3'=>$value3]; $connection->insert($tableName, $data);
Example :
$data = ["customer_id"=>'22',"status"=>1,'order_id'=>'455']; $lastInsertedID= $connection->insert($tableName, $data);
Update Query:
Syntax:
$data = ["column_name3"=>$value3]; $where = ['column_name = ?' => $value, 'column_name2 = ?' => $value2]; $updatedRows=$connection->update($tableName, $data, $where);
Example:
$data = ["status"=>1]; $where = ['customer_id = ?' => '22', 'order_id = ?' => '455']; $updatedRows=$connection->update($tableName, $data, $where);
Delete Queries
Syntax:
$connection->delete( $tableName, ['column_name = ?' => $value, 'column_name2 = ?' => '0'] );
Example :
$connection->delete( $tableName, ['customer_id = ?' => '22', 'status = ?' => '0'] );
1
0
aliumair
13th March 2025
Interesting post. I Have Been wondering about this issue, so thanks for posting. Pretty cool post.It 's really very nice and Useful post.Thanks 전당포 대출
Write a comment ...
Post comment
Cancel
Nimra
12th March 2025
Wow, cool post. I'd like to write like this too - taking time and real hard work to make a great article... but I put things off too much and never seem to get started. Thanks though.<a href="https://slotsonline.ph/casino/safe-and-reliable-slot-platform/">Philippines Safe and Reliable Slot Platform</a>
Write a comment ...
Post comment
Cancel
Nimra
12th March 2025
Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.online slot machine
Write a comment ...
Post comment
Cancel
Nimra
11th March 2025
Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also<a href="https://www.login.ps/blogs/115896/Any-Talent-for-Beer-making-Knowledge-Drip-Coffee-Makers">best coffee maker 2025</a>
Write a comment ...
Post comment
Cancel
Nimra
11th March 2025
I wanted to thank you for this excellent read!! I definitely loved every little bit of it. I have you bookmarked your site to check out the new stuff you post.cowhide jacket
Write a comment ...
Post comment
Cancel
Nimra
10th March 2025
Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.<a href="https://www.coursera.org/user/8ac24e36a3f6fd7c585d22a6776d0a8b">situs togel luar negeri</a>
Write a comment ...
Post comment
Cancel
mr
8th March 2025
I know this is one of the most meaningful information for me. And I'm animated reading your article. But should remark on some general things, the website style is perfect; the articles are great tko cartel4d
Write a comment ...
Post comment
Cancel
mr
8th March 2025
I know this is one of the most meaningful information for me. And I'm animated reading your article. But should remark on some general things, the website style is perfect; the articles are great tko cartel4d
Write a comment ...
Post comment
Cancel
Diana
7th March 2025
The Escorts Lucknow are for all kinds of needs and wishes, ranging from expensive pleasure seekers to budget clients. Having a wide array of agencies and independent operators to select from, you can choose a call girl that suits your individual needs and want. If you're seeking intimacy, companionship, or adult entertainment, these agencies offer a reliable and verified service.
Write a comment ...
Post comment
Cancel
kittu
6th March 2025
You made such an interesting piece to read, giving every subject enlightenment for us to gain knowledge. Thanks for sharing the such information with us to read this...
[url=https://fcajeep.com/]https://fcajeep.com/[/url]
Write a comment ...
Post comment
Cancel
hunain
2nd March 2025
A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. koitoto A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. koitoto
Write a comment ...
Post comment
Cancel
hunain
2nd March 2025
A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. olxtoto A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. koitoto
Write a comment ...
Post comment
Cancel
Nimra
2nd March 2025
Thank you very much for this great post. WOW The War Within carry services Thank you very much for this great post. koi toto Thank you very much for this great post. kikototo Thank you very much for this great post. Koitoto
Write a comment ...
Post comment
Cancel
Nimra
1st March 2025
Thanks, that was a really cool read! 飛行外套客製 Thanks, that was a really cool read! 團體外套 Thanks, that was a really cool read! 團體制服 Thanks, that was a really cool read! 團體服推薦
Write a comment ...
Post comment
Cancel
Nimra
1st March 2025
I wanted to thank you for this excellent read!! I definitely loved every little bit of it. I have you bookmarked your site to check out the new stuff you post.<a href="https://socialrus.com/story18627391/%ED%8E%98%EC%9D%B4%EC%A7%80-%EB%A0%88%EB%94%94-%EC%99%84%EB%B2%BD%ED%95%9C-%EB%9E%9C%EB%94%A9%ED%8E%98%EC%9D%B4%EC%A7%80%EB%A5%BC-%EB%A7%8C%EB%93%A4-%EC%88%98-%EC%9E%88%EB%8A%94-%EA%B3%B3">랜딩페이지 제작</a>
Write a comment ...
Post comment
Cancel
Please rotate your device
We don't support landscape mode on your device. Please rotate to portrait mode for the best view of our site